﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;

namespace RRC_ASP_DB_Test.DatabaseUtility
{
    public class RetreatUtility
    {
        private SqlConnection rrcDbConn;
        private SqlCommand rrcSqlCmd;

        public RetreatUtility()
        {
            rrcDbConn = new SqlConnection();
            rrcDbConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            rrcSqlCmd = new SqlCommand();
        }

        private void openConnection()
            {
                try
                {
                    rrcDbConn.Open();
                    Console.WriteLine(Environment.NewLine + "rrcDbConn.State: " + rrcDbConn.State.ToString());

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error opening connecdtion: " + ex.ToString());
                }
            }

            public void closeConnection()
            {
                rrcDbConn.Close();
                Console.WriteLine("rrcDbConn.State: " + rrcDbConn.State.ToString());
            }


            /// <summary>
            /// retrieve the retreats that match input retreate id
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            public RetreatInfo retrieveRetreatById(int targetId)
            {
                StringBuilder sqlText = new StringBuilder();
                sqlText.Append("SELECT r.retreatId, r.title, r.description, r.duration, cat.categoryDescription, r.activeData ");
                sqlText.Append("FROM Retreat r left join (");
		        sqlText.Append("select x.retreatId, c.shortDescription as categoryDescription ");
		        sqlText.Append("from RetreatCategoryXref x ");
		        sqlText.Append("inner join RetreatCategory c on c.categoryId=x.categoryId ");
		        sqlText.Append(String.Format("where x.retreatId={0}", targetId));
                sqlText.Append(") cat ");
                sqlText.Append("on r.retreatId=cat.retreatId ");
                sqlText.Append(String.Format("WHERE r.retreatId = {0}",targetId));

                SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

                List<RetreatInfo> retreats = retrieveRetreats(sqlCmd);

                if (retreats != null && retreats.Count > 0)
                {
                    return retreats[0];  // since retreat id is unique, only one row will be returned
                }
                else
                {
                    return null;
                }
            }

            /// <summary>
            /// retrieve retreats where title is like parameter
            /// </summary>
            /// <param name="targetTitle"></param>
            /// <returns></returns>
            public List<RetreatInfo> retrieveRetreatByTitle(string targetTitle)
            {
                SqlCommand sqlCmd = new SqlCommand(String.Format("SELECT r.retreatId, r.title, r.description, r.duration, c.shortDescription as catagoryDesc, r.activeData " +
                    "FROM Retreat r inner join RetreatCategory c on r.categoryId=c.categoryId " +
                    "WHERE r.title like '%{0}%';", targetTitle), rrcDbConn);

                return retrieveRetreats(sqlCmd);
            }

            /// <summary>
            /// retrieve retreats where catagory text is like parameter
            /// </summary>
            /// <param name="targetCategory"></param>
            /// <returns></returns>
            public List<RetreatInfo> retrieveRetreatByCategory(string targetCategory)
            {
                SqlCommand sqlCmd = new SqlCommand(String.Format("SELECT r.retreatId, r.title, r.description, r.duration, c.shortDescription as catagoryDesc, r.activeData " +
                    "FROM Retreat r inner join RetreatCategory c on r.categoryId=c.categoryId " +
                    "WHERE c.shortDescription like %{0}%;", targetCategory), rrcDbConn);

                return retrieveRetreats(sqlCmd);
            }

            private List<RetreatInfo> retrieveRetreats(SqlCommand sqlCmd)
            {
                if (rrcDbConn.State != ConnectionState.Open)
                {
                    openConnection();
                }

                SqlDataReader retreatReader = null;
                retreatReader = sqlCmd.ExecuteReader();
                RetreatInfo retreatInfo = null;
                List<RetreatInfo> retreats = new List<RetreatInfo>();

                while (retreatReader.Read())
                {
                    retreatInfo = new RetreatInfo();
                    retreatInfo.retreatid = (int)retreatReader["retreatId"];
                    retreatInfo.title = retreatReader["title"].ToString();
                    retreatInfo.description = retreatReader["description"].ToString();
                    retreatInfo.duration = (double)retreatReader["duration"];
                    retreatInfo.categoryDesc = retreatReader["categoryDescription"].ToString();

                    if (Convert.ToInt32(retreatReader["activeData"]) != 0)
                    {
                        retreatInfo.activeData = true;
                    }
                    else
                    {
                        retreatInfo.activeData = false;
                    }

                    retreats.Add(retreatInfo);

                }
                closeConnection();
                return retreats;
            }

            //public int insertRetreat(Retreat retreat)
            //{
            //    if (rrcDbConn.State != ConnectionState.Open)
            //    {
            //        openConnection();
            //    }

            //    try
            //    {
            //        String insertText = String.Format("INSERT INTO Retreat (title, description, duration, categoryId, activeData) " +
            //            "Values ('{0}','{1}',{2},{3}, 1);", retreat.title, retreat.description, retreat.duration, retreat.categoryId);
            //        SqlCommand sqlCmd = new SqlCommand(insertText, rrcDbConn);

            //        return sqlCmd.ExecuteNonQuery();
            //    }
            //    catch (Exception ex)
            //    {
            //        Console.WriteLine("Failed to insert.  exception=" + ex.ToString());
            //        return -1;
            //    }
            //    finally
            //    {
            //        closeConnection();
            //    }

            //}
        
    }
}